﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_ListDatabases]
#-- Purpose:		Provides a mechanism to list databases that can be restored
#--					via stored procedure call and the most recent date
#--	Last Update:	08/05/2012
#--					For a complete history - please review comments in SVN
#-- Called By:		Scheduled Job, Database Restore Utility
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_ListDatabases]
(
	@include_backup_info			bit		= 1
)
AS

SET NOCOUNT ON

IF @include_backup_info = 1
	SELECT		d.name database_name,
				(SELECT ISNULL(MAX(backup_start_date), '1/1/1900') FROM [msdb].[dbo].[backupset] WHERE database_name = d.name) latest_backup,
				STUFF((	SELECT	'|' + name
						FROM	(
								SELECT	DISTINCT name
								FROM	[msdb].[dbo].[backupset]
								WHERE	name IS NOT NULL
										AND name <> ''
										AND database_name = d.name
								UNION SELECT ''
								) t1
						ORDER BY LEN(name)
						FOR XML PATH(''))
					, 1, 1, '') as labels	
	FROM		[master].[sys].[databases] d
	WHERE		d.name NOT IN ('master', 'model', 'msdb', 'tempdb', DB_NAME())
				AND d.name NOT LIKE 'ReportServer$%'
ELSE
	SELECT		d.name database_name,
				NULL latest_backup,
				NULL as labels	
	FROM		[master].[sys].[databases] d
	WHERE		d.name NOT IN ('master', 'model', 'msdb', 'tempdb', DB_NAME())
				AND d.name NOT LIKE 'ReportServer$%'

SET NOCOUNT OFF